-- =============================================
-- Procedure to update PUblishedDateTime.
-- =============================================
CREATE         PROCEDURE amsp_CMUpdatePublishDate
    @InPublishRequestDetailId numeric = NULL,
  @InPublishRequestID numeric =  NULL
AS

BEGIN

  DECLARE
    @ContactID numeric,
    @PublishedDateTime datetime

  IF @InPublishRequestDetailId IS NOT NULL BEGIN
     SET @PublishedDateTime = GetDATE()
     UPDATE Publish_Request_Detail
        SET PublishedDateTime = @PublishedDateTime,
            PublishRequestStatusCode = 'P'
      WHERE PublishRequestDetailID = @InPublishRequestDetailId

     UPDATE Publish_Message_Log
        SET DisplayFlag = 'N'
      WHERE PublishRequestDetailID = @InPublishRequestDetailId
  
    SELECT @ContactID = a.ContactID
      FROM Publish_Request a, Publish_Request_Detail b
     WHERE a.PublishRequestID = b.PublishRequestID
       AND b.PublishRequestDetailID = @InPublishRequestDetailId

    EXECUTE amsp_CMFinalizePublish @InPublishRequestDetailId, @PublishedDateTime, @ContactID
  END
  IF @InPublishRequestID IS NOT NULL BEGIN
     UPDATE Publish_Request
        SET PublishedDateTime = GetDATE()
      WHERE PublishRequestID = @InPublishRequestID

     UPDATE Publish_Message_Log
        SET DisplayFlag = 'N'
      WHERE PublishRequestDetailID = 0
        AND PublishRequestID = @InPublishRequestID
  END
END

GO
GRANT EXECUTE ON  [dbo].[amsp_CMUpdatePublishDate] TO [IMIS]
GO